<?php
/**
 * @author vladone
 * @copyright 2007
 */
include ('include/biff.php');
include ('include/conf.php');
no_cache();
//definesc array pentru numele coloanelor din baza de date
$col = array();
$col['id'] = 'id';
$col['nume'] = 'Nume';
$col['marca'] = 'Marca';
$col['model'] = 'Model';
$col['companie'] = 'Company';
$col['imei'] = 'Imei';
$col['unlock_code'] = 'Unlock code';
$col['data_in'] = 'Data IN';
$col['data_out'] = 'Data OUT';
$col['cost'] = 'Pret';
$col['status'] = 'Status';
$col['data_plata'] = 'Payment date';
$col['comentariu'] = 'Informations';
$col['suma'] = 'Price';
//defineste functie de cautare ca sa schimbe headerul la excel
function cautaHeader($header,$array){
	foreach($array as $key => $value){
		if($key == $header){
			return $value;
		}
	}
	return $header;
}
//end
$id_connect = connect_to_database(HOST, USER, PASS, 'server_imei2');
//se verifica autentificarea
$sesiune = mysql_real_escape_string($_GET['sesiune']);
$operatie = $_GET['op'];
$str = "SELECT tip_cont FROM clienti WHERE sesiune = '$sesiune'";
$result = mysql_query($str, $id_connect);
$num_rows = mysql_num_rows($result);
if ($num_rows == 1)
{
    $value = mysql_fetch_assoc($result);
    $tip_cont = $value['tip_cont'];
    if ($tip_cont == 'admin')
    {
        //set $str pentru admin
		$str = executeAdmin($operatie);
		Export($operatie,$str,$id_connect,$col);
    }elseif($tip_cont == 'client'){
    	//set $str pentru client
	    $str = executeClient($operatie);
	    Export($operatie,$str,$id_connect,$col);
	}else{
		//nici o varianta
	    echo "mess=Cont necunoscut!&flag=0";
	    exit();
	}
}
else
{
    echo "mess=Login firs!&flag=0";
    exit();
}

//begin selection
function deleteFile($file){
	if (file_exists($file))
        {
            unlink($file);
            echo "flag=1";
            exit();
        }
        else
        {
            echo "mess=Eroare la stergerea fisierului&flag=0";
            exit();
        }
}
function executeAdmin($operatie)
{
	$str = '';
    if ($operatie == 'delete')
    {
        $filename = $_SERVER['DOCUMENT_ROOT'] . "/" . $_GET['filename'];
        deleteFile($filename);
        
    }
    if ($operatie == 'getImei')
    {
        $select = "SELECT id,nume,imei,unlock_code,model,companie,data_in,cost FROM coduri ";
        $where = "WHERE ";
        $where_array = array();
        $imei_status = $_GET['imeiStatus'];
        $nume = $_GET['nume'];
        $model = $_GET['model'];
        $companie = $_GET['companie'];
        if ($imei_status == "wait")
        {
            array_push($where_array, "status='wait'");
        }
        if ($imei_status == "done")
        {
            array_push($where_array, "status='done'");
        }
        if ($nume != "all")
        {
            array_push($where_array, "nume='$nume'");
        }
        if ($model != "all")
        {
            array_push($where_array, "model='$model'");
        }
        if ($companie != "all")
        {
            array_push($where_array, "companie='$companie'");
        }
        if (count($where_array) != 0)
        {
            for ($i = 0; $i < count($where_array); $i++)
            {
                $where .= $where_array[$i];
                if ($i < count($where_array) - 1)
                {
                    $where .= " AND ";
                }
            }
            $str = $select . $where;
        }
        else
        {
            $str = $select;
        }
        if(isset($_GET['dataStart'])&&isset($_GET['dataEnd'])){
        	$dataStart = strtotime($_GET['dataStart']);
        	$dataEnd = strtotime($_GET['dataEnd']);
        	$str .= " AND data_in BETWEEN '$dataStart' AND '$dataEnd'";
        	$str .= " ORDER BY companie ASC";
        }else{
        	$str .= " ORDER BY companie ASC LIMIT 1000";
        }
        
    }
    if ($operatie == 'getClienti')
    {
        $str = "SELECT ";
        $what = "";
        $selectWhat = array();
        if ($_GET['nume'] == 'true')
        {
            array_push($selectWhat, "nume");
        }
        if ($_GET['pass'] == 'true')
        {
            array_push($selectWhat, "parola");
        }
        if ($_GET['mail'] == 'true')
        {
            array_push($selectWhat, "mail");
        }
        if ($_GET['phone'] == 'true')
        {
            array_push($selectWhat, "phone");
        }
        if ($_GET['total'] == 'true')
        {
            array_push($selectWhat, "cr_total");
        }
        if ($_GET['ramas'] == 'true')
        {
            array_push($selectWhat, "cr_ramas");
        }
        if ($_GET['consumat'] == 'true')
        {
            array_push($selectWhat, "cr_consumat");
        }
        if (count($selectWhat) != 0)
        {
            for ($i = 0; $i < count($selectWhat); $i++)
            {
                $what .= $selectWhat[$i];
                if ($i < count($selectWhat) - 1)
                {
                    $what .= ",";
                }
            }
            $str = $str . $what . " FROM clienti ORDER BY nume ASC";
        }
        else
        {
            echo "mess=Selecteza macar o optiune&flag=0";
            exit();
        }
    }
    if ($operatie == 'getPlati')
    {
        if ($_GET['nume'] != 'all')
        {
            $nume = mysql_real_escape_string($_GET['nume'], $id_connect);
            $str = "SELECT nume, data_plata, comentariu, suma FROM plati WHERE nume='$nume' ORDER BY data_plata DESC";
        }
        else
        {
            $str = "SELECT nume, data_plata, comentariu, suma FROM plati ORDER BY data_plata DESC";
        }
    }
    if ($operatie == 'getCreditList')
    {
		$str = "SELECT marca, model, companie,cost from credit order by marca ASC";
	}
    //return result
    return $str;
}
function executeClient($operatie)
{
	$str = '';
	if ($operatie == 'delete')
    {
        $filename = $_SERVER['DOCUMENT_ROOT'] . "/" . $_GET['filename'];
        deleteFile($filename);
        
    }
	if ($operatie == 'clGetImei')
	{
		$searchImei = mysql_real_escape_string($_GET['searchImei']);
		$nume = mysql_real_escape_string($_GET['nume']);
		if ($searchImei != 'all'){
			$str = "SELECT marca,model,companie,cost,imei,unlock_code,data_in,status FROM coduri WHERE nume='$nume' AND imei LIKE '%$searchImei%' ORDER BY data_in DESC LIMIT 1000";
		}else{
			$str = "SELECT marca,model,companie,cost,imei,unlock_code,data_in,status FROM coduri WHERE nume='$nume' ORDER BY data_in DESC LIMIT 1000";
		}
	}
	if ($operatie == 'clGetPriceList')
	{
		$nume = mysql_real_escape_string($_GET['nume']);
		$str = "SELECT marca,model,companie,cost FROM credit_clienti WHERE nume='$nume' ORDER BY marca ASC, model ASC, companie ASC";
	}
	if ($operatie == 'clGetPlati')
	{
		$nume = mysql_real_escape_string($_GET['nume']);
		$str = "SELECT comentariu,suma,data_plata FROM plati WHERE nume='$nume' ORDER BY data_plata DESC";
	}
	
	return $str;
}
//functie export
function Export($operatie, $str, $id_connect, $col)
{
	$result = mysql_query($str,$id_connect);
	$myxls = new BiffWriter();
    // file name suggested to the browser (stream)
    $myxls->outfile = date("d-m-Y") . ".xls";

    // create 4 default fonts
    $myxls->xlsSetFont('Arial', 10, FONT_NORMAL);
    $myxls->xlsSetFont('Arial', 10, FONT_BOLD);
    $myxls->xlsSetFont('Courier New', 8, FONT_NORMAL);
    $myxls->xlsSetFont('Courier New', 8, FONT_BOLD);

    // set default row height to 14 points
    $myxls->xlsSetDefRowHeight(14);
    $myxls->xlsSetColWidth(1, 1, 20);
    $myxls->xlsSetColWidth(3, 3, 10);
    // turn  backup flag on (xlk)
    $myxls->xlsSetBackup();

    // print grid lines
    $myxls->xlsSetPrintGridLines();

    // print row (1,2...) and col (A, B..) references
    $myxls->xlsSetPrintHeaders();

    //print header
    $fields = mysql_num_fields($result);
    //$myxls->xlsSetColWidth(0,$fields,100);
    for ($i = 0; $i < $fields; $i++)
    {
    	$header = mysql_field_name($result, $i);
		$header = cautaHeader($header,$col);
        $myxls->xlsWriteText(0, $i, $header, 0, 0, FONT_0, ALIGN_CENTER);
    }
    //print data
    $linie = 1;
    while ($row = mysql_fetch_row($result))
    {
        // $coloana contorizeaza coloana pentru a gasi si formata data
        $coloana = 0;
        foreach ($row as $value)
        {
            if ((!isset($value)) or ($value == ""))
            {
                $myxls->xlsWriteText($linie, $coloana, "", 0, 0, FONT_0, ALIGN_CENTER);
            }
            else
            {
                if (eregi('data', mysql_field_name($result, $coloana)))
                {
                    $value = date("d-m-Y H:i", $value);
                }
                //$value = str_replace('"', '""', $value);
                //$value = '"' . $value . '"';
                $myxls->xlsWriteText($linie, $coloana, $value, 0, 0, FONT_0, ALIGN_CENTER);
            }
            $coloana += 1;
        }
        $linie += 1;
    }
    // assemble the stream
    // and either send the stream to the browser or
    // save it as a file in the location/filename passed via $fname
    $fname = date("d-m-Y", time()) . "_" . $operatie . "_" . random_str(2) . ".xls";
    $myxls->xlsParse($fname);
    //fisierul a fisierul a fost salvat pe hard. Se verifica daca exista si se intoarce mesaj catre client
	if (file_exists($fname))
    {
        echo "mess=$fname&flag=1";
        exit();
    }
    else
    {
        echo "mess=Error saving file!&flag=0";
        exit();
    }
    return;
}
?>